#!/usr/bin/env python3
# -*- coding: utf-8 -*-

__author__='Jack Zhang'

import asyncio,logging,aiomysql

def logSql(sql: str,args=()):
    logging.info('SQL:%s'%sql)  #输出SQL信息

#创建一个全局的连接池,每个HTTP请求都可以从连接池中直接获取数据库连接
#使用连接池的好处是不必频繁地打开和关闭数据库连接,而是能复用就尽量复用
async def createPool(loop,**kwargs):  #创建连接池
    logging.info('creating database connection pool...')
    global __pool
    __pool=await aiomysql.create_pool(
        host=kwargs.get('host','localhost'),
        port=kwargs.get('port',3306),
        user=kwargs['user'],password=kwargs['password'],
        db=kwargs['db'],
        charset=kwargs.get('charset','utf8'),
        autocommit=kwargs.get('autoccommit',True),
        maxsize=kwargs.get('maxsize',10),
        minsize=kwargs.get('minsize',1),
        loop=loop)

#要执行SELECT语句,我们用select函数执行,需要传入SQL语句和SQL参数
async def select(sql: str,args,size=None):
    logSql(sql=sql,args=args)
    global __pool
    async with __pool.get() as connection:
        async with connection.cursor(aiomysql.DictCursor) as cursor:
            await cursor.execute(sql.replace('?','%s'),args=args)
            rows=await (cursor.fetchmany(size) if size else cursor.fetchall())
        logging.info('rows returned:%s'%len(rows))
        return rows

#要执行INSERT,UPDATE,DELETE语句,可以定义一个通用的execute()函数,因为这3种SQL的执行都需要相同的参数,以及返回一个整数表示影响的行数
async def execute(sql: str,args,autocommit=True):
    logSql(sql=sql)
    global __pool
    async with __pool.get() as connection:
        if not autocommit: await connection.begin()
        try:
            async with connection.cursor(aiomysql.DictCursor) as cursor:
                await cursor.execute(sql.replace('?','%s'),args=args)
                affectedRows=cursor.rowcount
            if not autocommit: await connection.commit()
        except BaseException:
            if not autocommit: await connection.rollback()
            raise BaseException
        return affectedRows

#生成以?作为参数的字符串
def createArgsString(num):
    L=[]
    for n in range(num):
        L.append('?')
    return ','.join(L)

#Field和各种Field子类

class Field(object):
    def __init__(self,name,column_type,primary_key,default):
        self.name=name
        self.column_type=column_type
        self.primary_key=primary_key
        self.default=default

    def __str__(self):
        return '<%s,%s:%s>'%(self.__class__.__name__,self.column_type,self.name)

class StringField(Field):
    def __init__(self,name=None,primary_key=False,default=None,ddl='varchar(100)'):
        super().__init__(name,ddl,primary_key,default)


class BooleanField(Field):
    def __init__(self,name=None,default=False):
        super().__init__(name,'boolean',False,default)


class IntegerField(Field):
    def __init__(self,name=None,primary_key=False,default=0):
        super().__init__(name,'bigint',primary_key,default)


class FloatField(Field):
    def __init__(self,name=None,primary_key=False,default=0.0):
        super().__init__(name,'real',primary_key,default)


class TextField(Field):
    def __init__(self,name=None,default=None):
        super().__init__(name,'text',False,default)

#通过metaclass将具体的子类如User的映射信息读取出来
class ModelMetaclass(type):
    def __new__(cls,name,bases,attrs):
        if name=="Model":#排除Model类本身
            return type.__new__(cls,name,bases,attrs)
        tableName=attrs.get('__table__',name)#获取table名称
        logging.info('ModelMetaclass found model:%s(table:%s)'%(name,tableName))
        #获取所有的Field和主键名
        mappings:dict={}
        fields:list=[]
        primaryKey=None
        for key,value in attrs.items():
            if isinstance(value,Field):
                logging.info('  ModelMetaclass found mapping:%s==>%s'%(key,value))
                mappings[key]=value
                if value.primary_key:
                    #找到主键
                    if primaryKey:
                        raise RuntimeError('Duplicate primary key for field:%s'%key)
                    primaryKey=key
                else:fields.append(key)
        if not primaryKey:
            raise RuntimeError('Primary key not found.')
        for key in mappings.keys():
            attrs.pop(key)
        escapedFields=list(map(lambda f:'`%s`'%f,fields))
        attrs['__mappings__']=mappings#保存属性和列的映射关系
        attrs['__table__']=tableName
        attrs['__primary_key__']=primaryKey#主键属性名
        attrs['__fields__']=fields#除主键外的其他属性名
        #构造默认的SELECT,INSERT,UPDATE,DELETE语句
        attrs['__select__']='select `%s`,%s from `%s`'%(primaryKey,','.join(escapedFields),tableName)
        attrs['__insert__']='insert into `%s` (%s,`%s`) values (%s)'%(tableName,','.join(escapedFields),primaryKey,createArgsString(len(escapedFields)+1))
        attrs['__update__']='update `%s` set %s where `%s`=?'%(tableName,', '.join(map(lambda f:'%s=?'%f,escapedFields)),primaryKey)
        attrs['__delete__']='delete from `%s` where `%s`=?'%(tableName,primaryKey)
        return type.__new__(cls,name,bases,attrs)

class Model(dict,metaclass=ModelMetaclass):
    def __init__(self,**kwargs):
        super(Model,self).__init__(**kwargs)

    def __getattr__(self,key):
        try:
            return self[key]
        except KeyError:
            raise AttributeError("'Model' object has no attribute '%s'"%key)

    def __setattr__(self,key,value):
        self[key]=value

    def getValue(self,key):
        return getattr(self,key,None)

    def getValueOrDefault(self,key):
        value=getattr(self,key,None)
        if value is None:
            field=self.__mappings__[key]
            if field.default is not None:
                value=field.default() if callable(field.default) else field.default
                logging.debug('Model using default value for %s:%s'%(key,str(value)))
                setattr(self,key,value)
        return value

    @classmethod
    async def findAll(cls,where=None,args=None,**kw):
        sql=[cls.__select__]
        if where:
            sql.append('where')
            sql.append(where)
        if args is None:args=[]
        orderBy=kw.get('orderBy',None)
        if orderBy:
            sql.append('order by')
            sql.append(orderBy)
        limit=kw.get('limit',None)
        if limit is not None:
            sql.append('limit')
            if isinstance(limit,int):
                sql.append('?')
                args.append(limit)
            elif isinstance(limit,tuple) and len(limit)==2:
                sql.append('?,?')
                args.extend(limit)
            else:raise ValueError('Invalid limit value:%s'%str(limit))
        rows=await select(' '.join(sql),args)
        return [cls(**row) for row in rows]

    @classmethod
    async def findNumber(cls,selectField,where=None,args=None):
        sql=['select %s _num_ from `%s`'%(selectField,cls.__table__)]
        if where:
            sql.append('where')
            sql.append(where)
        rows=await select(' '.join(sql),args,1)
        if len(rows)==0:return None
        return rows[0]['_num_']

    @classmethod
    async def find(cls,pk):
        rows=await select('%s where `%s`=?'%(cls.__select__,cls.__primary_key__),[pk],1)
        if len(rows)==0:return None
        return cls(**rows[0])

    async def save(self):
        args=list(map(self.getValueOrDefault,self.__fields__))
        args.append(self.getValueOrDefault(self.__primary_key__))
        rows=await execute(self.__insert__,args)
        if rows!=1:logging.warning('failed to insert record:affected rows:%s'%rows)

    async def update(self):
        args=list(map(self.getValue,self.__fields__))
        args.append(self.getValue(self.__primary_key__))
        rows=await execute(self.__update__,args)
        if rows!=1:logging.warning('failed to update by primary key:affected rows:%s'%rows)

    async def remove(self):
        args=[self.getValue(self.__primary_key__)]
        rows=await execute(self.__delete__,args)
        if rows!=1:logging.warning('failed to remove by primary key:affected rows:%s'%rows)